package com.joy;

import com.google.common.base.Preconditions;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.joy.common.DBTYPE;
import com.joy.common.JdbcMeta;

import org.apache.commons.cli.CommandLine;
import org.apache.commons.cli.CommandLineParser;
import org.apache.commons.cli.DefaultParser;
import org.apache.commons.cli.HelpFormatter;
import org.apache.commons.cli.Option;
import org.apache.commons.cli.Options;
import org.apache.commons.cli.ParseException;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Hello world!
 */
public final class App {
    private static final Logger LOGGER = LogManager.getLogger(LogManager.ROOT_LOGGER_NAME);  
    public static String HELP_STRING = null;
    public static String ONEBLANK=" ";

    private App() {
    }

    public static HelpFormatter help = new HelpFormatter();


    /**
     * dbtype   set database type
     * dbname     mssql is database name , oracle is tnsname
     * hostname   oracle not need  but need tns_admin
     * port       oracle not need   but need tns_admin
     * table
     * username 
     * password
     * @param args
     */
    public static void main(String[] args) {
        String CMDLineSyntax = "example: -dbtype mssql  -dbname demo  -username SA -password Welcome1 -host localhost -port 1433 ";
        Options opts = new Options();
        opts.addOption(new Option("h", "help",  false, "print help for the command."));
        opts.addOption(Option.builder("dbtype").required().hasArg().desc("source database type,now support MYSQL,ORACLE,MSSQL ").build());
        opts.addOption(Option.builder("dbname").required().hasArg().desc("source database name or service name or tnsname depend on dbtype").build());
        opts.addOption(Option.builder("tables").hasArgs().optionalArg(true).valueSeparator(',').argName("table1,table2,table3").desc("if not specified select all tables").build());
        opts.addOption(Option.builder("host").hasArg().desc("source database hostname ").build());
        opts.addOption(Option.builder("port").hasArg().desc("source database port ").build());
        opts.addOption(Option.builder("username").required().hasArg().desc("database username").build());
        opts.addOption(Option.builder("password").required().hasArg().desc("database password").build());

        CommandLineParser parser = new  DefaultParser();
        CommandLine line = null;
        try {
            line = parser.parse(opts, args);
            if(line.hasOption("h")){
                help.printHelp(CMDLineSyntax, opts);
            }

            String dbtype = line.getOptionValue("dbtype");
            String dbname = line.getOptionValue("dbname");
            String host = line.getOptionValue("host");
            String port = line.getOptionValue("port");
            String[] tables = line.getOptionValues("tables");
            String username = line.getOptionValue("username");
            String password = line.getOptionValue("password");
            
            String jdbcurl = null;
            DBTYPE c_dbtype = DBTYPE.valueOf(dbtype.toUpperCase());
            try{
                
                String driverName = JdbcMeta.DRIVER_NAME.get(c_dbtype);
                if (Strings.isNullOrEmpty(driverName))  throw new Exception("can't get the  driver name of  the"+ dbtype);
                Class.forName(driverName);

                String jdbcTpl = JdbcMeta.JDBCURL_TPL.get(c_dbtype);
                if (Strings.isNullOrEmpty(driverName))  throw new Exception("can't get the jdbc url of  the"+ dbtype);
                jdbcurl = String.format(jdbcTpl,host,port,dbname);
            }catch(Exception e){
                LOGGER.error(e.getMessage());
            }

            try {
                Connection conn = DriverManager.getConnection(jdbcurl,username,password);
                String catalog = conn.getCatalog();
                String schema = conn.getSchema();
                DatabaseMetaData dbmd = conn.getMetaData();
                
                List<String> tablenames = null;
                if(tables != null && tables.length>0){
                    tablenames = Lists.newArrayList(tables);
                }else{
                    tablenames = new ArrayList<>();
                    String[] types = { "TABLE" };  
                    ResultSet rs = dbmd.getTables(catalog, schema, "%", types); 
                    
                    while (rs.next()) {  
                        String tableName = rs.getString("TABLE_NAME");  //表名  
                        tablenames.add(tableName);
                    } 
                }
                
                String sql = null;
                switch(c_dbtype){
                    case MSSQL:  sql = fromMssqltoOracle(tablenames,conn); LOGGER.info(sql.toString());
                    case MYSQL:  sql = fromMysqltoOracle(tablenames,conn); LOGGER.info(sql.toString());
                    case ORACLE: return; //TODO
                }
                

            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (ParseException e) {
            help.printHelp(CMDLineSyntax, opts);
        }
    }


    public static String fromMssqltoOracle(List<String> tablenames, Connection conn) throws SQLException{
        Preconditions.checkNotNull(conn, "database connection  is  null");
        Preconditions.checkArgument(tablenames.size() > 0,"tablenames size is 0");

        String catalog = conn.getCatalog();
        String schema = conn.getSchema();
        DatabaseMetaData dbmd = conn.getMetaData();
        StringBuilder sql = new StringBuilder();

        for(String tablename: tablenames){
            ResultSet colSet = dbmd.getColumns(catalog, schema, tablename, "%");
            sql.append("CREATE TABLE ").append(tablename).append(" ("); 
            while(colSet.next()){
                String colName = colSet.getString("COLUMN_NAME");
                sql.append(colName).append(ONEBLANK);
                String colType = colSet.getString("TYPE_NAME").toLowerCase();
                String colSize = colSet.getString("COLUMN_SIZE");
                String colDigit = colSet.getString("DECIMAL_DIGITS");

                switch(colType){
                    case "bigint":    sql.append("NUMBER(19)").append(","); break;
                    case "binary":    sql.append("RAW(50)").append(","); break;
                    case "bit":       sql.append("NUMBER(2)").append(",");break;
                    case "char":      sql.append("char(").append(colSize).append("),");break;
                    case "datetime":  sql.append("DATE").append(",");break;
                    case "decimal":   sql.append("NUMBER(").append(colSize).append(",").append(colDigit).append("),");break;
                    case "real":      sql.append("BINARY_FLOAT").append(",");break;
                    case "image":     sql.append("BLOB").append(",");break;
                    case "int":       sql.append("NUMBER(10)").append(",");break;
                    case "money":     sql.append("NUMBER(19,4)").append(",");break;
                    case "nchar":     sql.append("NCHAR(").append(colSize).append("),");break;
                    case "ntext":     sql.append("NCLOB").append(",");break;
                    case "numeric":   sql.append("NUMBER(").append(colSize).append(",").append(colDigit).append("),");break;
                    case "nvarchar":  
                        if(Long.parseLong(colSize)> 4000){
                            sql.append("NCLOB").append(",");break;
                        }else{
                            sql.append("NVARCHAR2(").append(colSize).append("),");break;
                        }
                    case "smalldatetime":    sql.append("DATE").append(",");break;
                    case "smallint":  sql.append("NUMBER(5)").append(",");break;
                    case "smallmoney":sql.append("NUMBER(10,4)").append(",");break;
                    case "sql_variant":sql.append("BLOB").append(",");break;
                    case "text":      sql.append("CLOB").append(",");break;
                    case "timestamp": sql.append("RAW(8)").append(",");break;
                    case "tinyint":   sql.append("NUMBER(3)").append(",");break;
                    case "uniqueidentifier":  sql.append("BLOB").append(",");break;
                    case "varbinary": sql.append("RAW(50)").append(",");break;
                    case "varchar":   
                        long size = Math.round(Double.parseDouble(colSize)*1.5+0.5);
                        if(size > 4000){
                            sql.append("NCLOB").append(",");break;
                        }else{
                            sql.append("VARCHAR2(").append(size).append("),");break;
                        }
                }

            }
            sql.deleteCharAt(sql.length()-1).append(");\n");

            ResultSet primaryKeys =  dbmd.getPrimaryKeys(catalog, schema, tablename);
            List<String> primaryList = new ArrayList<>();
            while(primaryKeys.next()){
                String colName = primaryKeys.getString("COLUMN_NAME");
                primaryList.add(colName);
            }

            StringBuilder  altersql = new StringBuilder("alter table ")
                                        .append(tablename)
                                        .append(" add constraint ")
                                        .append(tablename+"_pk")
                                        .append(" primary key ( ");
            if(primaryList.size()>0){
                for(String pricol: primaryList){
                    altersql.append(pricol).append(",");
                }
                altersql.deleteCharAt(sql.length()-1).append(");\n");
                sql.append(altersql);
            }

           
        }
       
        return sql.toString();
    }

    public static String fromMysqltoOracle(List<String> tablenames, Connection conn) throws Exception {
        Preconditions.checkNotNull(conn, "database connection  is  null");
        Preconditions.checkArgument(tablenames.size() > 0,"tablenames size is 0");

        String catalog = conn.getCatalog();
        String schema = conn.getSchema();
        DatabaseMetaData dbmd = conn.getMetaData();
        StringBuilder sql = new StringBuilder();

        for(String tablename: tablenames){
            ResultSet colSet = dbmd.getColumns(catalog, schema, tablename, "%");
            sql.append("CREATE TABLE ").append(tablename).append(" ("); 
            while(colSet.next()){
                String colName = colSet.getString("COLUMN_NAME");
                sql.append(colName).append(ONEBLANK);
                String colType = colSet.getString("TYPE_NAME").toLowerCase();
                String colSize = colSet.getString("COLUMN_SIZE");
                String colDigit = colSet.getString("DECIMAL_DIGITS");

                switch(colType){
                    // int
                    case "bigint":    sql.append("NUMBER(20)").append(","); break;
                    case "bigint unsigned":    sql.append("NUMBER(20)").append(","); break;
                    case "int":       sql.append("NUMBER(10)").append(",");break;
                    case "meduimint": sql.append("NUMBER(7)").append(",");break;
                    case "smallint":  sql.append("NUMBER(5)").append(",");break;
                    case "tinyint":   sql.append("NUMBER(3)").append(",");break;
                    //  string
                    case "varchar":   
                        long size = Math.round(Double.parseDouble(colSize)*1.5+0.5);
                        if(size > 4000){
                            sql.append("CLOB").append(",");
                        }else{
                            sql.append("VARCHAR2(").append(size).append("),");
                        }; break;
                    case "char":      sql.append("CHAR(").append(colSize).append("),");break;
                    case "text":      sql.append("CLOB").append(","); break;
                    case "longtext":  sql.append("CLOB").append(","); break;
                    // date
//                    case "datetime":  sql.append("DATE").append(",");break;
//                    case "date":      sql.append("DATE").append(",");break;
//                    case "time":      sql.append("DATE").append(",");break;
//                    case "timestamp": sql.append("DATE").append(",");break;
                    case "datetime":  sql.append("TIMESTAMP").append(",");break;
                    case "date":      sql.append("TIMESTAMP").append(",");break;
                    case "time":      sql.append("TIMESTAMP").append(",");break;
                    case "timestamp": sql.append("TIMESTAMP").append(",");break;
                    // float
                    case "decimal":   sql.append("NUMBER(").append(colSize).append(",").append(colDigit).append("),");break;
                    case "float":     sql.append("NUMBER(").append(colSize).append(",").append(colDigit==null?"6":colDigit).append("),");break;
                    case "double":    sql.append("NUMBER(").append(colSize).append(",").append(colDigit==null?"12":colDigit).append("),");break;

                    //binary
                    case "blob":      sql.append("BLOB").append(","); break;
                    case "longblob":  sql.append("BLOB").append(","); break;
                    case "bit":
                                      int bytesizes =1;
                                        if(colSize != null){
                                            bytesizes = (Integer.parseInt(colSize)+ 7) / 8;
                                        }
                                      sql.append("RAW(").append(bytesizes).append("),");break;
                    case "binary":    sql.append("RAW(").append(colSize).append("),");break;
                    default:throw new Exception("not support "+colType);
                }

            }
            sql.deleteCharAt(sql.length()-1).append(");\n");

            ResultSet primaryKeys =  dbmd.getPrimaryKeys(catalog, schema, tablename);
            List<String> primaryList = new ArrayList<>();
            while(primaryKeys.next()){
                String colName = primaryKeys.getString("COLUMN_NAME");
                primaryList.add(colName);
            }

            StringBuilder  altersql = new StringBuilder("alter table ")
                                        .append(tablename)
                                        .append(" add constraint ")
                                        .append(tablename+"_pk")
                                        .append(" primary key (");
            if(primaryList.size()>0){
                for(String pricol: primaryList){
                    altersql.append(pricol).append(",");
                }
                altersql.deleteCharAt(altersql.length()-1).append(");\n");
                sql.append(altersql);
            }

           
        }
        return sql.toString();
    }

}
